﻿using System;
using System.Collections.Generic;
using Net.Studio.Forms;
using Spire.Xls;
using System.IO;

namespace DataModelWF
{
    public class ExcelModelManager : IData
    {
        public DBModel Load(string s)
        {
            try
            {
                //创建Workbook对象并加载Excel文档
                Workbook workbook = new Workbook();
                string path = Directory.GetCurrentDirectory();
                string strfile = path + @"\" + "fluxWMS.xlsx";
                workbook.LoadFromFile(strfile, ExcelVersion.Version2013);

                DBModel dbModel = new DBModel();
                dbModel = SheetToDbModel(true, workbook, dbModel);
                return dbModel;
            }
            catch (Exception ex)
            {
                return null;
            }

        }

        /// <summary>
        /// 从EXCEL导入到Model中
        /// </summary>
        /// <param name="hasTitle">有标题否</param>
        /// <param name="book"Excel</param>
        /// <param name="dbModel">Model</param>
        /// <returns></returns>
        private DBModel SheetToDbModel(bool hasTitle, Workbook book, DBModel dbModel)
        {
            try
            {
                int imax = book.Worksheets.Count;
                for (int k = 1; k < imax; k++)
                {
                    Worksheet sheet = book.Worksheets[k];

                    int iRowCount = sheet.Rows.Length;
                    int iColCount = sheet.Columns.Length;
                    int rcol = 0;
                    int rrow = 0;
                    int rflag = 0; //当此值为5时退出

                    TableModel tblModel = null;
                    for (int i = 0; i < iRowCount; i++)
                    {
                        var cellRange = sheet.Range[i + 1, 1];
                        //字体为粗体，则表示是表名
                        if ((cellRange.Style.Font.FontName == "Arial" || cellRange.Style.Font.FontName == "微软雅黑" || cellRange.Style.Font.FontName == "华文新魏") &&
                            cellRange.Style.Font.Size == 16)
                        {
                            //dt = new DataTable();
                            var tablenoname = sheet.Range[i + 1, 1].Text + "," + sheet.Range[i + 1, 2].Text;
                            tblModel = new TableModel(tablenoname);
                            if (!dbModel.Tables.ContainsKey(sheet.Range[i + 1, 1].Text))
                                dbModel.Tables.Add(sheet.Range[i + 1, 1].Text, tblModel);
                            rcol = i + 1;
                            rflag = 0;
                        }
                        //当循环的i等于rcol时，表示这一行就是列头
                        if (i == rcol)
                        {
                           rrow = i + 1;
                        }

                        //当循环的i大于行rrow时表示是数据行，在遇到字体为Arial，16或第一列的值为空时结束
                        if (i > rcol)
                        {
                            cellRange = sheet.Range[i + 1, 1];
                            if ((cellRange.Style.Font.FontName == "Arial" && cellRange.Style.Font.Size == 16) ||
                                string.IsNullOrEmpty(cellRange.Text)
                                )
                            {
                                rflag++;
                                if (rflag < 5)
                                    continue;
                                else
                                    break;
                            }
                            else
                            {

                                //字段 描述  数据类型 主键  允许为空 唯一健
                                ////CustomerID 客户ID    varchar(30) Y
                                //private string id = string.Empty;
                                //private string dTFNo = string.Empty;
                                //private string dTFName = string.Empty;
                                //private string fullDTFType = string.Empty;
                                //private string dataLength = string.Empty;
                                //private string decLength = string.Empty;
                                //private string defaultValue = string.Empty;
                                //private string isNotNull = string.Empty;
                                //private string indentKey = string.Empty;
                                //private string primaryKey = string.Empty;

                                string id = Guid.NewGuid().ToString();
                                string dTFNo = sheet.Range[i + 1, 1].Text;
                                string dTFName = !string.IsNullOrEmpty(sheet.Range[i + 1, 2].Text) ? sheet.Range[i + 1, 2].Text : dTFNo;
                                string vtype = sheet.Range[i + 1, 3].Text;  //varchar(30),decimal(18, 8)
                                string fullDTFType = "";
                                string lendec = "";
                                if (vtype.IndexOf('(') > 0)
                                {
                                    fullDTFType = vtype.Split('(')[0];
                                    lendec = vtype.Split('(')[1].Replace(")", "");
                                }
                                else
                                {
                                    fullDTFType = vtype;
                                    lendec = "";
                                }

                                string dataLength = "";
                                string decLength = "";
                                if (lendec.IndexOf(",") > 0)
                                {
                                    dataLength = lendec.Split(',')[0];
                                    decLength = lendec.Split(',')[1];
                                }
                                else
                                    dataLength = lendec;
                                string defaultValue = "";
                                string isNotNull = sheet.Range[i + 1, 5].Text;
                                string primaryKey = sheet.Range[i + 1, 4].Text;
                                string indentKey = sheet.Range[i + 1, 6].Text;

                                TableModelField tblModFld = new TableModelField();
                                tblModFld.Id = id;
                                tblModFld.DTFNo = dTFNo;
                                tblModFld.DTFName = dTFName;
                                tblModFld.FullDTFType = fullDTFType;
                                tblModFld.DataLength = dataLength;
                                tblModFld.DecLength = decLength;
                                tblModFld.DefaultValue = defaultValue;
                                tblModFld.IsNotNull = isNotNull;
                                tblModFld.IndentKey = indentKey;
                                tblModFld.PrimaryKey = primaryKey;
                                if (tblModel != null)
                                {
                                    if (!tblModel.Fields.ContainsKey(tblModel.No + "_" + dTFNo))
                                        tblModel.Fields.Add(tblModel.No + "_" + dTFNo, tblModFld);
                                }
                            }
                        }
                    }
                }
                return dbModel;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public bool Save(DBModel db)
        {
            try
            {
                string path = Directory.GetCurrentDirectory();
                Workbook workbook = exportTofileFromDbModel(db);
                string strfile = path + @"\" + "db_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
                workbook.SaveToFile(strfile,ExcelVersion.Version2013);

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 将Model导出到Excel
        /// </summary>
        /// <param name="dbModel">Model</param>
        /// <returns></returns>
        private Workbook exportTofileFromDbModel(DBModel dbModel)
        {
            Workbook book = new Workbook();

            CellStyle oddStyle = book.Styles.Add("oddStyle");
            oddStyle.Font.Size = 10;
            oddStyle.Font.FontName = "Arial";

            int imax = dbModel.Tables.Count;
            int i = 1;
            int irow = 1; //行号
            int icol = 0; //序号

           foreach(KeyValuePair<string,TableModel> kv in dbModel.Tables) {
               TableModel tblModel = kv.Value;
                if (tblModel != null)
                {

                    Worksheet sheet = book.Worksheets.Add(i.ToString() + "." + tblModel.Name);

                    irow = 1;
                    i = i + 1;
                    var cellRange0 = sheet.Range[irow, 1];
                    cellRange0.Style.Font.FontName = "Arial";
                    cellRange0.Style.Font.Size = 16;
                    cellRange0.RowHeight = 20.25;
                    cellRange0.Text = tblModel.No;

                    var cellRange1 = sheet.Range[irow, 2];
                    cellRange1.Style.Font.FontName = "华文新魏";
                    cellRange1.Style.Font.Size = 16;
                    cellRange1.Text = tblModel.Name;

                    irow = irow + 1;

                    //字段
                    var cellRange_field = sheet[irow, 1]; //字段
                    cellRange_field.Style.Font.Size = 12;
                    cellRange_field.Style.Font.FontName = "Arial";
                    cellRange_field.Style.KnownColor = ExcelColors.Aqua;
                    cellRange_field.Text = "字段";

                    var cellRange_desc = sheet[irow, 2]; //描述
                    cellRange_desc.Style.Font.Size = 12;
                    cellRange_desc.Style.Font.FontName = "Arial";
                    cellRange_desc.Style.KnownColor = ExcelColors.Aqua;
                    cellRange_desc.Text = "描述";

                    var cellRange_type = sheet[irow, 3]; //数据类型
                    cellRange_type.Style.Font.Size = 12;
                    cellRange_type.Style.Font.FontName = "Arial";
                    cellRange_type.Style.KnownColor = ExcelColors.Aqua;
                    cellRange_type.Text = "数据类型";

                    var cellRange_pk = sheet[irow, 4]; //主键
                    cellRange_pk.Style.Font.Size = 12;
                    cellRange_pk.Style.Font.FontName = "Arial";
                    cellRange_pk.Style.KnownColor = ExcelColors.Aqua;
                    cellRange_pk.Text = "主键";

                    var cellRange_isnotnull = sheet[irow, 5]; //允许为空
                    cellRange_isnotnull.Style.Font.Size = 12;
                    cellRange_isnotnull.Style.Font.FontName = "Arial";
                    cellRange_isnotnull.Style.KnownColor = ExcelColors.Aqua;
                    cellRange_isnotnull.Text = "允许为空";

                    var cellRange_ak = sheet[irow, 6]; //唯一健
                    cellRange_ak.Style.Font.Size = 12;
                    cellRange_ak.Style.Font.FontName = "Arial";
                    cellRange_ak.Style.KnownColor = ExcelColors.Aqua;
                    cellRange_ak.Text = "唯一健";
                    irow = irow + 1;

                    foreach (KeyValuePair<string, TableModelField> fv in tblModel.Fields)
                    {

                        // sheet.SetDefaultRowStyle(irow, oddStyle);
                        //sheet.SetRowHeight(irow, 16.25);

                       string id = fv.Value.Id;
                       string dTFNo = fv.Value.DTFNo;
                       string dTFName = fv.Value.DTFName;
                       string fullDTFType = fv.Value.FullDTFType;
                       string dataLength =  fv.Value.DataLength;
                       string decLength = fv.Value.DecLength;
                       string defaultValue = fv.Value.DefaultValue;
                       string isNotNull = fv.Value.IsNotNull;
                       string indentKey =fv.Value.IndentKey;
                       string primaryKey = fv.Value.PrimaryKey;
                       string type = string.IsNullOrEmpty(fullDTFType) ? "" : fullDTFType;
                       if (type != "")
                       {
                            if (!string.IsNullOrEmpty(dataLength))
                            {
                                if (!string.IsNullOrEmpty(decLength))
                                    type += "(" + dataLength + "," + decLength + ")";
                                else
                                    type += "(" + dataLength + ")";
                            }
                        }
                        var cellRange_field0 = sheet[irow, 1]; //字段
                        cellRange_field0.Text = string.IsNullOrEmpty(dTFNo) ? "" : dTFNo;

                        var cellRange_desc0 = sheet[irow, 2]; //描述
                        cellRange_desc0.Text = string.IsNullOrEmpty(dTFName) ? "" : dTFName;

                        var cellRange_type0 = sheet[irow, 3]; //数据类型
                        cellRange_type0.Text = type;

                        var cellRange_pk0 = sheet[irow, 4]; //主键
                        cellRange_pk0.Text = string.IsNullOrEmpty(primaryKey) ? "" : primaryKey;

                        var cellRange_isnotnull0 = sheet[irow, 5]; //允许为空
                        cellRange_isnotnull0.Text = string.IsNullOrEmpty(isNotNull) ? "" : isNotNull;

                        var cellRange_ak0 = sheet[irow, 6]; //唯一健
                        cellRange_ak0.Text = string.IsNullOrEmpty(indentKey) ? "" : indentKey;
                        irow = irow + 1;
                    }
                    sheet.AllocatedRange.AutoFitColumns();//自动调整列的宽度去适应单元格的数据
                    sheet.AllocatedRange.AutoFitRows();//自动调整行的高度去适应单元格的数据

                    irow = irow + 1;
                }
                
            }

            book.Worksheets["Sheet1"].Remove();
            book.Worksheets["Sheet2"].Remove();
            book.Worksheets["Sheet3"].Remove();

            return book;
        }

    }
}
